package dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import vo.FileVO;
import db.MySql;

public class FileDTO {
	
	FileVO vo = null;
	
	Connection conn = null;
	
	public FileDTO() {
		
		conn = new MySql().getConn();
	}
	
	//해당 아이디의 유저의 다운로드 목록을 셀렉트 해오는 메소드 입니다.
	public ArrayList<FileVO> downloadListSelect(String m_id){
		
		ArrayList<FileVO> list = new ArrayList<FileVO>();
		String sql = "select * from file where file_download_employee_id = '"+m_id+"' and file_division = 'download' order by file_download_date desc ";
		
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			
			while(rs.next()){
				
				FileVO vo = new FileVO();
				vo.setFile_download_employee_name(rs.getString("file_download_employee_name"));
				vo.setFile_download_employee_id(rs.getString("file_download_employee_id"));
				vo.setFile_upload_employee_id(rs.getString("file_upload_employee_id"));
				vo.setFile_download_filename(rs.getString("file_download_filename"));
				vo.setFile_download_date(rs.getString("file_download_date").substring(0, 10));
			
				list.add(vo);
			}
			
			rs.close();
			psmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("업로드 목록 셀렉트시 오류가 발생했습니다.");
		}
		
		return list;
	}
	
	//해당 아이디의 유저의 업로드 목록을 셀렉트 해오는 메소드 입니다.
	public ArrayList<FileVO> uploadListSelect(String m_id){
		
		ArrayList<FileVO> list = new ArrayList<FileVO>();
		String sql = "select * from file where file_upload_employee_id = '"+m_id+"' and file_division = 'upload' order by file_upload_date desc ";
		
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			
			while(rs.next()){
				
				FileVO vo = new FileVO();
				vo.setFile_upload_employee_name(rs.getString("file_upload_employee_name"));
				vo.setFile_upload_employee_id(rs.getString("file_upload_employee_id"));
				vo.setFile_upload_filename(rs.getString("file_upload_filename"));
				vo.setFile_upload_date(rs.getString("file_upload_date").substring(0, 10));
			
				list.add(vo);
			}
			
			rs.close();
			psmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("업로드 목록 셀렉트시 오류가 발생했습니다.");
		}
		
		return list;
	}
	
	public ArrayList<FileVO> select() {
		
		//page();
		ArrayList<FileVO> list = new ArrayList<FileVO>();
		String sql = "select * from file";
		
		try {			
			PreparedStatement psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			
			while(rs.next()){
				
				FileVO vo = new FileVO();
				vo.setFile_upload_employee_id(rs.getString("file_upload_employee_id"));
				vo.setFile_download_employee_id(rs.getString("file_download_employee_id"));
				vo.setFile_upload_employee_name(rs.getString("file_upload_employee_name"));
				vo.setFile_download_employee_name(rs.getString("file_download_employee_name"));
				vo.setFile_url(rs.getString("file_url"));
				vo.setFile_upload_filename(rs.getString("file_upload_filename"));
				vo.setFile_download_filename(rs.getString("file_download_filename"));
				if(rs.getString("file_upload_date") != null){
					vo.setFile_upload_date(rs.getString("file_upload_date").substring(0, 19));
				}
				if(rs.getString("file_division").equals("download")){					
					vo.setFile_download_date(rs.getString("file_download_date").substring(0, 19));
				}
				vo.setFile_download_count(rs.getInt("file_download_count"));
				vo.setFile_division(rs.getString("file_division"));
				
				list.add(vo);
			}
			
			rs.close();
			psmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//페이지에 관련된 변수
	int listSize = 10;  //페이지당 표시되는 행수
	int blockSize = 5; //한블럭당 표시되는 페이지 수
	int totSize = 0;   //전체 게시물의 갯수
	int totPage = 0;   //전체 페이지 수
	int totBlock = 0;  //전체 블럭 수
	int nowPage = 0;   //현재 페이지
	int nowBlock = 0;  //현재 블럭
	int startNo = 0;   //리스트 목록의 시작 위치
	int endNo = 0;     //리스트 목록의 끝 위치
	int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
	int endPage = 0;   //현재 블럭에서 표시되는 끝 블럭 번호
	
	//페이지 관련 연산
public void page(){
		
		String sql = "select count(*) cnt from board where mid like ?";
		
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%"+vo.getFind()+"%");
			
			ResultSet rs = psmt.executeQuery();
			
			while(rs.next()){
				
				totSize = rs.getInt("cnt");
				//전체 페이지 수
				totPage = (int)(Math.ceil(totSize*1.0 / listSize));
				//전체 블럭 수
				totBlock = (int)(Math.ceil(totPage*1.0 / blockSize));
				//현재 블럭
				nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize);
				//페이지의 시작과 끝 번호
				endPage = nowBlock * blockSize;
				startPage = endPage - blockSize +1;
				//게시물의 시작 번호와 끝 번호
				endNo = nowPage * listSize;
				startNo = endNo - listSize +1;
				//endPage, endNo 보정값
				if(endPage>totPage){
					endPage = totPage;
				}
				if(endNo>totSize){
					endNo = totSize;
				}
			}
			
			System.out.println(("totSize= "+totSize));
			System.out.println(("totPage= "+totPage));
			System.out.println(("totBlock= "+totBlock));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public FileVO getVo() {
		return vo;
	}

	public void setVo(FileVO vo) {
		this.vo = vo;
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
}
